DROP DATABASE IF EXISTS `VirginieExotique`;
CREATE DATABASE IF NOT EXISTS `VirginieExotique` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `VirginieExotique`;

-- --------------------------------------------------------

CREATE TABLE `user_ve` (
  `id_user` int(11) NOT NULL auto_increment,
  `nom_user` text NOT NULL,
  `prenom_user` text NOT NULL,
  `datenaissance_user` DATE NOT NULL,
  `sexe_user`  enum('Homme','Femme') NOT NULL,
  `numtel_user` VARCHAR(20),
  `pseudo_user` varchar(255) NOT NULL,
  `email_user` varchar(255) NOT NULL,
  `password_user` varchar(255) NOT NULL,
  `statut_user`  enum('client','Employer','Admin') NOT NULL,
  /*`adress_ve` int(11) NOT NULL,*/
  `ip_user` int(11) NOT NULL,
  `token_user` varchar(255) NOT NULL, 
  `date_inscription` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*ALTER TABLE `user_ve` ADD COLUMN numtel_user VARCHAR(20);*/

--
-- Déchargement des données de la table `user`
--


CREATE TABLE `client_ve` (
 `id_user` int(11) NOT NULL auto_increment,
  `nom_user` text NOT NULL,
  `prenom_user` text NOT NULL,
  `datenaissance_user` DATE NOT NULL,
  `sexe_user`  enum('Homme','Femme') NOT NULL,
  `numtel_user` VARCHAR(20),
  `pseudo_user` varchar(255) NOT NULL,
  `email_user` varchar(255) NOT NULL,
  `password_user` varchar(255) NOT NULL,
  `statut_user`  enum('client','Employer','Admin') NOT NULL,
  /*`adress_ve` int(11) NOT NULL,*/
  `ip_user` int(11) NOT NULL,
  `token_user` varchar(255) NOT NULL, 
  `date_inscription` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `employer_ve` (
  `id_user` int(11) NOT NULL auto_increment,
  `nom_user` text NOT NULL,
  `prenom_user` text NOT NULL,
  `datenaissance_user` DATE NOT NULL,
  `sexe_user`  enum('Homme','Femme') NOT NULL,
  `numtel_user` VARCHAR(20),
  `pseudo_user` varchar(255) NOT NULL,
  `email_user` varchar(255) NOT NULL,
  `password_user` varchar(255) NOT NULL,
  `statut_user`  enum('client','Employer','Admin') NOT NULL,
  `adress_ve` int(11) NOT NULL,
  `ip_user` int(11) NOT NULL,
  `token_user` varchar(255) NOT NULL, 
  `date_inscription` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `address_ve` (
  `id_address` int(11) NOT NULL auto_increment,
  `id_user` int(11) NOT NULL,
  `Pays_ou_region` varchar(255) NOT NULL,
  `Adresse` varchar(255) NOT NULL,
  `Adresse2` varchar(255) NOT NULL,
  `Ville` varchar(255) NOT NULL,
  `Etat_Province_Region` int(11) NOT NULL,
  `Code_postale` varchar(255) NOT NULL,
  PRIMARY KEY (`id_address`) ,
  foreign key (`id_user`) references user_ve (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `cb_user` (
  `id_cb` int(11) NOT NULL auto_increment,
  `id_user` int(11) NOT NULL,
  `num_cb` varchar(255) NOT NULL,
  `cryptogram_code` varchar(255) NOT NULL,
  `date_exp` DATE NOT NULL,
  `nom_cb` text NOT NULL,
  `prenom_cb` text NOT NULL,
  PRIMARY KEY (`id_cb`) ,
  foreign key (`id_user`) references user_ve (`id_user`)
  foreign key (`id_address`) references user_ve (`id_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `commande` (
  `id_commande` int(11) NOT NULL auto_increment,
  `id_user` int(11) NOT NULL,
  `date_commande` varchar(255) NOT NULL,
  `statue` varchar(255) NOT NULL,
  `quantiter_commander` DATE NOT NULL,
  `total_commande` DATE NOT NULL,
  `mode_paiement` DATE NOT NULL,
  `date_commande` text NOT NULL,
  `adresse_livraison` text NOT NULL,
  PRIMARY KEY (`id_cb`) ,
  foreign key (`id_user`) references user_ve (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `categorie` (
  `id_categorie` int(11) NOT NULL auto_increment,
  `img_categorie` varchar(255) NOT NULL,
  `nom_categorie` varchar(255) NOT NULL,
  `desc_categorie` varchar(255) NOT NULL,
  `ref_categorie` varchar(255) NOT NULL,
  `date_inscription` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_categorie`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*CREATE TABLE `stock` (
  `id_stock` int(11) NOT NULL auto_increment,
  `id_produit` int(11) NOT NULL,
  `quantiter` int(11) NOT NULL,
  PRIMARY KEY (`id_stock`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
*/

CREATE TABLE `produit` (
  `id_produit` int(11) NOT NULL auto_increment,
  `nom_produit` varchar(255) NOT NULL,
  `img_produit` varchar(255) NOT NULL,
  `desc_produit` varchar(255) NOT NULL,
  `ref_produit` varchar(255) NOT NULL,
  `prix_produit` varchar(255) NOT NULL,
  `date_inscription` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_produit`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 DROP TRIGGER IF EXISTS insert_client;
DELIMITER //
CREATE TRIGGER insert_client
BEFORE INSERT ON client_ve
FOR EACH ROW
BEGIN
    if new.id_user is null or new.id_user in (select id_user from user_ve) or new.id_user = 0
        then
            set new.id_user = ifnull((select id_user from user_ve where id_user >= all(select id_user from user_ve)), 0) + 1;
    end if;
    insert into user_ve values (new.id_user, new.nom_user, new.prenom_user, new.datenaissance_user, new.sexe_user, new.numtel_user, new.pseudo_user, new.email_user, new.password_user, new.statut_user, new.ip_user, new.token_user, new.date_inscription);
END //
DELIMITER ;

 DROP TRIGGER IF EXISTS insert_employer;
DELIMITER //
CREATE TRIGGER insert_employer
BEFORE INSERT ON employer_ve
FOR EACH ROW
BEGIN
    if new.id_user is null or new.id_user in (select id_user from user_ve) or new.id_user = 0
        then
            set new.id_user = ifnull((select id_user from user_ve where id_user >= all(select id_user from user_ve)), 0) + 1;
    end if;
    insert into user_ve values (new.id_user, new.nom_user, new.prenom_user, new.datenaissance_user, new.sexe_user, new.numtel_user, new.pseudo_user, new.email_user, new.password_user, new.statut_user, new.ip_user, new.token_user, new.date_inscription);
END //
DELIMITER ;










/*DROP TRIGGER IF EXISTS insert_into_employer_ve;
DELIMITER //
CREATE TRIGGER insert_into_employer_ve
AFTER INSERT ON user_ve
FOR EACH ROW
BEGIN
    IF NEW.statut_user = 'Employer' THEN
        INSERT INTO employer_ve (nom_user, prenom_user, datenaissance_user, sexe_user, numtel_user, pseudo_user, email_user, password_user, statut_user, ip_user, token_user, date_inscription)
        VALUES (NEW.nom_user, NEW.prenom_user, NEW.datenaissance_user, NEW.sexe_user, NEW.numtel_user, NEW.pseudo_user, NEW.email_user, NEW.password_user, NEW.statut_user, NEW.ip_user, NEW.token_user, NEW.date_inscription);
    END IF;
END//
DELIMITER ;
DROP TRIGGER IF EXISTS insert_into_client_ve;
DELIMITER //
CREATE TRIGGER insert_into_client_ve
AFTER INSERT ON user_ve
FOR EACH ROW
BEGIN
    IF NEW.statut_user = 'client' THEN
        INSERT INTO client_ve (nom_user, prenom_user, datenaissance_user, sexe_user, numtel_user, pseudo_user, email_user, password_user, statut_user, ip_user, token_user, date_inscription)
        VALUES (NEW.nom_user, NEW.prenom_user, NEW.datenaissance_user, NEW.sexe_user, NEW.numtel_user, NEW.pseudo_user, NEW.email_user, NEW.password_user, NEW.statut_user, NEW.ip_user, NEW.token_user, NEW.date_inscription);
    END IF;
END//
DELIMITER ;*/












INSERT INTO `user_ve` (`id_user`, `nom_user`, `prenom_user`, `datenaissance_user`, `sexe_user`, `numtel_user`, `pseudo_user`, `email_user`, `password_user`, `statut_user`, `ip_user`, `token_user`, `date_inscription`) VALUES 
(null, 'KULASEGARAM', 'Thiviyan', '2002-10-03', 'Homme', '0766191965', 'thivi2002', 'thiviyan@admin.com', '$2y$12$i54F96eBFFentAjYK9ILX.Az9obKASC5bHNZo.ymvQJQNq3THJKSW', "Admin", 123456789, '7aa9aded0569a54b91518b545e309b1ddce50edd7ced5580a1bed3fc9abca1d2ebfc9ea7d124a6ba954a40a89b38c25519b79598c2bd9485fe72bae39792f6b6', '2022-06-20 12:01:32');

INSERT INTO `client_ve` (`id_user`, `nom_user`, `prenom_user`, `datenaissance_user`, `sexe_user`, `numtel_user`, `pseudo_user`, `email_user`, `password_user`, `statut_user`, `ip_user`, `token_user`, `date_inscription`) VALUES 
(null, 'KULASEGARAM', 'Thiviyan', '2002-10-03', 'Homme', '0766191965', 'thivi2002', 'velthiviyan@gmail.com', '$2y$12$i54F96eBFFentAjYK9ILX.Az9obKASC5bHNZo.ymvQJQNq3THJKSW', "client", 0, '7aa9aded0569a54b91518b545e309b1ddce50edd7ced5580a1bed3fc9abca1d2ebfc9ea7d124a6ba954a40a89b38c25519b79598c2bd9485fe72bae39792f6b6', '2024-01-17 12:01:32'),
(null, 'VVVV', 'Nnnn', '1997-12-20', 'Homme', '0745789848', 'vvvv', 'velthiiyan@gmail.com', '$2y$12$xWADqLoSRpN7t65Amn60fel6lXo7EcWZQdJQtRCTCinuJse3cVb9u', "client", 0, '0ofc245502a0ae044', '2024-01-17 12:03:34'),
(null, 'BONOBO', 'Jean', '2000-12-20', 'Homme', '0745789848', 'bonobo2000', 'jbonobo.bambou@yahoo.com', '$2y$12$pbBB/OHUAT8dlto.biesZeMAlTGUg0F8kxNsCqLyljwoQgQ4okwA6', "client", 0, '951fb21b81920dffa71716c15442d2670d1938b3875b1adaec67015b74302f7f067cb3c4adf98c73ae25893b20fff01e9a02c03c4f4327a74ef0f94906f68df8', '2024-01-17 12:20:01'),
(null, 'EXEMPLE', 'Exemple', '2024-05-12', 'Homme', '0745789848', 'bibi', 'gcj@gmail.com', '$2y$12$P4ymg.S3.ZV6pyluAZl51eKVfRflptf4xtkMrVEYDIfvzbpLwQKnS', "client", 0, 'e0d21f9d397d87053cdd362e2809930ff631b47063b89463aa4bf6639a3d9896ee68ba071524628075c7cd408f31b6567991a25fbbdf4c41b583f38b8ae08aeb', '2024-01-18 10:26:13'),
(null, 'POMODORO', 'Jacky', '1975-12-30', 'Homme', '0745789848', 'jacky33', 'jacky33@wanadoo.fr', '$2y$12$glUe0F.04oF5DwNDRcSDrumYeNwc2Ymh41rwOSlCZQQxPNv.SURDC', "client", 0, '7aff4bf47bf8fc86c9012774df778eae30836c86768e7e65c070511381682e13c840282f3e8bc16d0bf40e020b0b24763e975f983c4710e3721951c20048a5aa', '2024-01-18 14:15:57'),
(null, 'EXEMPLE', 'Exemple', '2000-12-12', 'Femme', '0745789848', 'felmme2', 'felme@wanadoo.com', '$2y$12$PPB4K8N.kpnAApxxy4eF/.wdz9CRyXd0EduH16cHUtyega93eOFVe', "client", 0, '2aa1eb3c11c7d09ddce025e909c307427636a5a93feb55b7414b575a236e59d62bc36b62ed6dead4e0c7776ffe3d61bdcfd018d582f6fec41205c715c43d7ae4', '2024-01-24 17:26:18'),
(null, 'BONOBO', 'Bertrant', '2000-10-02', 'Homme', '0745789848', 'bonoboo50', 'bonoboo50@gmail.com', '$2y$12$yO5soMdUDLvdJ4I4.Jyod.qGFhSdjx.fDI0dLbOkpI9p3DMWas4NK', "client", 0, '90a4cdf42b28dd8149fff56aef79e25b24bd69e600da0b21d5342bac27f1ea1593f017287ed31d06a4ebe008b425a1395ceb16314db0e3a47c4205288f57d80b', '2024-01-26 12:41:50'),
(null, 'THIVIYAN', 'Kulasegaram', '2002-10-03', 'Homme', '0766191965', 'thivi20002', 'thivi@gmail.com', '$2y$12$1Y.1EcGab7bTDpNKt9E6..6ZnuDtHNeCAMtQz4G7MC4rw5Cf.yFJi', "client", 0, '422bb33dc76795ec634f2b55b96daf976995e8c0d0edc81dc2059345c6ed7c2d3256a49a574aa02c40d1506acdc263b55bc3c0225c55fe213fe82aced49210da', '2024-01-26 12:44:54'),
(null, 'SAM', 'Sam', '2001-02-01', 'Homme', '0656897440', 'sam123', 'sal123@gmail.com', '$2y$12$Yjc9luh5Wgq1aAMubzZUy.ySpoSAeqnOU5ptF3nM2jLl8SnQ.az7S', "client", 0, 'dd7b32f03f9b0f48492d19b7852fc630b99e873655434acb3ede4abee84be7f80b3b6d87f1cd56e69e671a72371684ec6b4b596f3f6077d1ca123001d61e8237', '2024-01-29 15:52:55'),
(null, 'POTIER', 'Alexandre', '1989-05-04', 'Homme', '0759837278', 'petitpot89', 'alexandre.potier@gmail.com', '$2y$12$hQMAZg1IITHDMUhDy7a7.u5JELw3ZNGVejg2nCe/HXWHTnexfHLPG', "client", 0, '346b0ed2b08c55f0e4336b745b953a01b4cb530729b0ae430695186b5bb9b44bb74ecd7eb51f7cdd630f433c594ac277c6d9839e5d509913307264d3dc5f33ad', '2024-03-12 14:47:33'),
(null, 'HELLE', 'Zoe', '2000-10-05', 'Femme', '0798565087', 'zoey2000', 'zoe.helle@gmail.com', '$2y$12$9X.1SYPg6M.L0K.s0k/7Tu7ACDgRyHbDmIEIiSjwyOyokvf1zSrHm', "client", 0, 'e09cd26c19c00be8339b8c027426c2ac4bc5c5a89ca898ad0d91ce4a17f064591b44ec59d87ebf2df1d1d20cd73f155bb409a54f82a659a61a67959cc978f85f', '2024-04-05 14:58:24'),
(null, 'BONJOUR ', 'Mamadou', '2002-02-12', 'Homme', '789456123', 'mamadoo2003', 'mamadoo2003@gmail.com', '$2y$12$Ov0rgKP9o9Ujp8WLodco7uEwbyW9n.6RqFM4hLk18.DQRl7ir.h3G', 'client', 0, 'b75361a62f605bf7b93d56a6a36cfc000ba7b33ebd1226b2413d68e8b6c026f51a12d0ed91cca19b87102bb716576ae604617dfd5569586b60191d9775e97739', '2024-04-29 09:44:58'),
(null, 'GOGOIM', 'Amin', '2002-02-20', 'Homme', '123456789', 'amin2002', 'amin2002@gmail.com', '$2y$12$Cem0X3tr5GgnABIpqSEBbOrRIjVWmWNh2guwBxLFAKU9KXJHN3Gt6', 'client', 0, '87a9651bd30f8651deda6c323b67826d3cc90590b12b65bfac2b216f1eb81e288dc0667993733a608013f560115a1ba977ddccd5b8bfcb5925dfa26f07993e78', '2024-05-02 09:36:54'),
(null, 'VV', 'Vvv', '2002-10-20', 'Homme', '123', 'vvvv123', 'vvv123@gmail.com', '$2y$12$PVV3fDYjh.fXyVKVu0zQ5.Dj4dZKIb8L3BnBjnd52RUXaqhYEuaJS', 'client', 0, '9b6902507acd177236350dca8be4784b835a8b150d44f0d7f0036a97666f9eddb54472a0f7f56770487c493adb1287d19c13ade95b5f72ea6bd8df83d8bc8503', '2024-05-02 09:38:19'),
(null, 'CLIENT', 'Client', '2002-10-20', 'Homme', '78987452156236', 'client2002', 'client2002@gmail.com', '$2y$12$IgvWx6bo5Q520mSh7L3GyOPLH5blgiz0XiW2MYInWh66Wct.BQmJy', 'client', 0, '9a5b7ec6e7ef68daf90cc856657103e80226115e2f0070dcc6984229149c2d93353518f9d64037dd354f9162cd21b469347a6882a112301fd0d82e915039aa82', '2024-05-02 11:06:26');


INSERT INTO `employer_ve` (`id_user`, `nom_user`, `prenom_user`, `datenaissance_user`, `sexe_user`, `numtel_user`, `pseudo_user`, `email_user`, `password_user`, `statut_user`, `ip_user`, `token_user`, `date_inscription`) VALUES 
(null, 'BONOBO', 'Balnabe', '2004-10-03', 'Homme', '0752190865', 'barnabe2004', 'barnabe2004@gmail.com', '$2y$12$i54F96eBFFentAjYK9ILX.Az9obKASC5bHNZo.ymvQJQNq3THJKSW', 'Employer', 0, '0', '2024-04-25 11:05:32'),
(null, 'EMPLOYER', 'Employer', '2000-03-12', 'Homme', '987621987452', 'employer2000', 'employer2000@gmail.com', '$2y$12$5UzKfLI6OlUw.3qTlKqAjO8nHPJlkt00sUjLxRdHsugs/77gc3aUi', 'Employer', 0, 'fc245502a0ae044cf9aea0cf616d959f89a7d57b3ad40f94ab842b7a844f84daa85f6b4996d756080c90ff00db0ed54b0ebd0d7ca562639039af6f09fcb7e537', '2024-05-02 15:36:45');



--
-- Index pour la table `categorie_produit`
--
ALTER TABLE `categorie_produit`
  ADD PRIMARY KEY (`idcategorie_produit`);


-- Index pour la table `cb`
--
ALTER TABLE `cb`
  ADD PRIMARY KEY (`idCB`);

--


ALTER TABLE `categorie_produit`
  MODIFY `idcategorie_produit` int(11) NOT NULL AUTO_INCREMENT;


-- AUTO_INCREMENT pour la table `produits`
--
ALTER TABLE `produits`
  MODIFY `idproduits` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

